package com.lm.common.poi;

import com.lm.common.excelbean.*;
import com.lm.model.*;
import com.lm.util.ExcelBaseBean;
import com.lm.util.ExcelSheet;
import com.lm.util.ExcelSheetTitle;
import com.lm.vo.CourseSimple;
import com.lm.vo.DmMajorSimple;
import com.lm.vo.MajorSimple;
import com.lm.vo.UniversitySimple;
import org.apache.poi.hpsf.DocumentSummaryInformation;
import org.apache.poi.hpsf.SummaryInformation;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;
import org.apache.poi.ss.usermodel.CellType;
import org.apache.poi.ss.usermodel.FillPatternType;
import org.apache.poi.ss.usermodel.IndexedColors;
import org.springframework.http.HttpHeaders;
import org.springframework.http.HttpStatus;
import org.springframework.http.MediaType;
import org.springframework.http.ResponseEntity;
import org.springframework.web.multipart.MultipartFile;

import java.io.ByteArrayOutputStream;
import java.io.IOException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**

 */
public class PoiUtils {

    /**
     * @return
     * @Author geekw
     * @Description //TODO
     * @Date 16:56 2019/4/8
     * @Param
     */
    public static ResponseEntity<byte[]> exportExcelTemplate(ExcelBaseBean excelBaseBean) {
        HttpHeaders headers = null;
        ByteArrayOutputStream baos = null;
        try {
            //1.创建Excel文档
            HSSFWorkbook workbook = new HSSFWorkbook();
            //2.创建文档摘要
            workbook.createInformationProperties();
            //3.获取文档信息，并配置
            DocumentSummaryInformation dsi = workbook.getDocumentSummaryInformation();
            //3.1文档类别
            dsi.setCategory(excelBaseBean.getCategory());
            //3.2设置文档管理员
            dsi.setManager(excelBaseBean.getManager());
            //3.3设置组织机构
            dsi.setCompany(excelBaseBean.getCompany());
            //4.获取摘要信息并配置
            SummaryInformation si = workbook.getSummaryInformation();
            //4.1设置文档主题
            si.setSubject(excelBaseBean.getSubject());
            //4.2.设置文档标题
            si.setTitle(excelBaseBean.getTitle());
            //4.3 设置文档作者
            si.setAuthor(excelBaseBean.getAnthor());
            //4.4设置文档备注
            si.setComments(excelBaseBean.getCommnets());
            //创建日期显示格式
            HSSFCellStyle dateCellStyle = workbook.createCellStyle();
            dateCellStyle.setDataFormat(HSSFDataFormat.getBuiltinFormat("m/d/yy"));
            //创建标题的显示样式
            HSSFCellStyle headerStyle = workbook.createCellStyle();
            headerStyle.setFillForegroundColor(IndexedColors.YELLOW.index);
            headerStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
            for (ExcelSheet excelSheet : excelBaseBean.getSheets()) {
                //创建Excel表单
                HSSFSheet sheet = workbook.createSheet(excelSheet.getCreateSheet());
                List<ExcelSheetTitle> sheetTitles = excelSheet.getSheetTitles();
                for (ExcelSheetTitle title : sheetTitles) {
                    sheet.setColumnWidth(title.getColumn(), title.getColumnWidth());//设置宽度
                }
                HSSFRow headerRow = sheet.createRow(0);//设置表头
                for (ExcelSheetTitle title : sheetTitles) {
                    HSSFCell cell = headerRow.createCell(title.getColumn());
                    cell.setCellValue(title.getTitle());
                    cell.setCellStyle(headerStyle);
                }
                /*装数据*/
            }
            headers = new HttpHeaders();
            headers.setContentDispositionFormData("attachment", new String(excelBaseBean.getExcelName().getBytes("UTF-8"), "iso-8859-1"));
            headers.setContentType(MediaType.APPLICATION_OCTET_STREAM);
            baos = new ByteArrayOutputStream();
            workbook.write(baos);
        } catch (IOException e) {
            e.printStackTrace();
        }
        return new ResponseEntity<byte[]>(baos.toByteArray(), headers, HttpStatus.CREATED);
    }

    public static List<TEntityUniversity> importUniversity2List(MultipartFile file, List<TDmDistrict> districts) {
        List<TEntityUniversity> universities = new ArrayList<>();
        try {
            HSSFWorkbook workbook = new HSSFWorkbook(new POIFSFileSystem(file.getInputStream()));
            int numberOfSheets = workbook.getNumberOfSheets();
            for (int i = 0; i < numberOfSheets; i++) {
                HSSFSheet sheet = workbook.getSheetAt(i);
                int physicalNumberOfRows = sheet.getPhysicalNumberOfRows();
                TEntityUniversity university;
                for (int j = 0; j < physicalNumberOfRows; j++) {
                    if (j == 0) {
                        continue;//标题行
                    }
                    HSSFRow row = sheet.getRow(j);
                    if (row == null) {
                        continue;//没数据
                    }
                    int physicalNumberOfCells = row.getPhysicalNumberOfCells();
                    university = new TEntityUniversity();
                    for (int k = 0; k < physicalNumberOfCells; k++) {
                        HSSFCell cell = row.getCell(k);
                        switch (cell.getCellTypeEnum()) {
                            case STRING: {
                                String cellValue = cell.getStringCellValue();
                                if (cellValue == null) {
                                    cellValue = "";
                                }
                                switch (k) {
                                    case 0:
                                        university.setName(cellValue);
                                        break;
                                    case 1:
                                        int provinceIndex = districts.indexOf(new TDmDistrict(cellValue));
                                        university.setProvinceId(districts.get(provinceIndex).getId());
                                        break;
                                    case 2:
                                        int cityIndex = districts.indexOf(new TDmDistrict(cellValue));
                                        university.setCityId(districts.get(cityIndex).getId());
                                        break;
                                    case 3:
                                        university.setType(cellValue);
                                        break;
                                    case 4:
                                        university.setJubanType(cellValue);
                                        break;
                                    case 5:
                                        university.setYuanxiaoType(cellValue);
                                        break;
                                    case 6:
                                        university.setAttribute(cellValue);
                                        break;
                                    case 7:
                                        university.setBanxueType(cellValue);
                                        break;
                                }
                            }
                            break;
                        }
                    }
                    universities.add(university);
                }
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        return universities;
    }

    public static List<TEntityMajor> importMajor2List(MultipartFile file, List<TDmMajor> dmMajors, List<UniversitySimple> universities) {
        List<TEntityMajor> majors = new ArrayList<>();
        try {
            HSSFWorkbook workbook = new HSSFWorkbook(new POIFSFileSystem(file.getInputStream()));
            int numberOfSheets = workbook.getNumberOfSheets();
            for (int i = 0; i < numberOfSheets; i++) {
                HSSFSheet sheet = workbook.getSheetAt(i);
                int physicalNumberOfRows = sheet.getPhysicalNumberOfRows();
                TEntityMajor major;
                for (int j = 0; j < physicalNumberOfRows; j++) {
                    if (j == 0) {
                        continue;//标题行
                    }
                    HSSFRow row = sheet.getRow(j);
                    if (row == null) {
                        continue;//没数据
                    }
                    int physicalNumberOfCells = row.getPhysicalNumberOfCells();
                    major = new TEntityMajor();
                    for (int k = 0; k < physicalNumberOfCells; k++) {
                        HSSFCell cell = row.getCell(k);
                        switch (cell.getCellTypeEnum()) {
                            case STRING: {
                                String cellValue = cell.getStringCellValue();
                                if (cellValue == null) {
                                    cellValue = "";
                                }
                                switch (k) {
                                    case 0:
                                        major.setName(cellValue);
                                        break;
                                    case 1:
                                        major.setEnglishName(cellValue);
                                        break;
                                    case 2:
                                        int universityIndex = universities.indexOf(new UniversitySimple(cellValue));
                                        major.setUniversityId(universities.get(universityIndex).getId());
                                        break;
                                    case 3:
                                        major.setLevel(cellValue);
                                        break;
                                    case 4:
                                        major.setRank(cellValue);
                                        break;
                                    case 5:
                                        major.setMajorBanxueType(cellValue);
                                        break;
                                    case 6:
                                        int majorIndex = dmMajors.indexOf(new TDmMajor(cellValue));
                                        TDmMajor dmMajor = dmMajors.get(majorIndex);
                                        major.setDiscipline(dmMajor.getId());
                                        major.setCode(dmMajor.getMajorcode());
                                        break;
                                }
                            }
                            break;
                        }
                    }
                    majors.add(major);
                }
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        return majors;

    }

    public static List<TEntityCourse> importCourse2List(MultipartFile file, List<TEntityCourseType> courseTypes) {
        List<TEntityCourse> courses = new ArrayList<>();
        try {
            HSSFWorkbook workbook = new HSSFWorkbook(new POIFSFileSystem(file.getInputStream()));
            int numberOfSheets = workbook.getNumberOfSheets();
            for (int i = 0; i < numberOfSheets; i++) {
                HSSFSheet sheet = workbook.getSheetAt(i);
                int physicalNumberOfRows = sheet.getPhysicalNumberOfRows();
                TEntityCourse course;
                for (int j = 0; j < physicalNumberOfRows; j++) {
                    if (j == 0) {
                        continue;//标题行
                    }
                    HSSFRow row = sheet.getRow(j);
                    if (row == null) {
                        continue;//没数据
                    }
                    int physicalNumberOfCells = row.getPhysicalNumberOfCells();
                    course = new TEntityCourse();
                    for (int k = 0; k < physicalNumberOfCells; k++) {
                        HSSFCell cell = row.getCell(k);
                        switch (cell.getCellTypeEnum()) {
                            case STRING: {
                                String cellValue = cell.getStringCellValue();
                                if (cellValue == null) {
                                    cellValue = "";
                                }
                                switch (k) {
                                    case 0:
                                        course.setName(cellValue);
                                        break;
                                    case 1:
                                        course.setEnglishName(cellValue);
                                        break;
                                    case 2:
                                        course.setCode(cellValue);
                                        break;
                                    case 3:
                                        int courseTypeIndex = courseTypes.indexOf(new TEntityCourseType(cellValue));
                                        TEntityCourseType courseType = courseTypes.get(courseTypeIndex);
                                        course.setType(courseType.getId());
                                        break;
                                }
                            }
                            break;
                            case NUMERIC:
                                /*Double numericCellValue = cell.getNumericCellValue();
                                if (numericCellValue == null) {
                                    numericCellValue = 0.0;
                                }*/
                                switch (k) {
                                    case 2:
                                        course.setCode(cell.getNumericCellValue() + "");
                                        break;
                                }
                        }
                    }
                    courses.add(course);
                }
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        return courses;
    }

    public static PyfaBean importTrainingPlan2List(MultipartFile file,List<MajorSimple> majorSimples,List<CourseSimple> courseSimples,List<TEntityCourseType> courseTypes) {
        PyfaBean pyfaBean = new PyfaBean();
        try {
            HSSFWorkbook workbook = new HSSFWorkbook(new POIFSFileSystem(file.getInputStream()));
            pyfaBean.setPyfaZsBean(sheet1(workbook,majorSimples));
            pyfaBean.setSemesterWeeks(sheet2(workbook));
            pyfaBean.setMainCourses(sheet3(workbook,courseSimples));
            pyfaBean.setTrainingRequires(sheet4(workbook));
            pyfaBean.setCoursePlans(sheet5(workbook,courseSimples));
            pyfaBean.setCourseStructions(sheet6(workbook,courseTypes));
            pyfaBean.setGxjzs(sheet7(workbook,courseSimples));
        }catch (Exception e) {
            e.printStackTrace();
        }
        return pyfaBean;
    }

    private static PyfaZsBean sheet1(HSSFWorkbook workbook, List<MajorSimple> majorSimples) {
        PyfaZsBean pyfaZsBean = new PyfaZsBean();
        try {
            HSSFSheet sheet = workbook.getSheetAt(0);
            int physicalNumberOfRows = sheet.getPhysicalNumberOfRows();
            for (int j = 0; j < physicalNumberOfRows; j++) {
                if (j == 0) {
                    continue;//标题行
                }
                HSSFRow row = sheet.getRow(j);
                if (row == null) {
                    continue;//没数据
                }
                int physicalNumberOfCells = row.getPhysicalNumberOfCells();
                for (int k = 0; k < physicalNumberOfCells; k++) {
                    HSSFCell cell = row.getCell(k);
                    switch (cell.getCellTypeEnum()) {
                        case STRING: {
                            String cellValue = cell.getStringCellValue();
                            if (cellValue == null) {
                                cellValue = "";
                            }
                            switch (k) {
                                case 0:
                                    pyfaZsBean.setName(cellValue);
                                    break;
                                case 1:
                                    pyfaZsBean.setEnglishName(cellValue);
                                    break;
                                case 2:
                                    pyfaZsBean.setYear(cellValue);
                                    break;
                                case 3:
                                    int majorIndex = majorSimples.indexOf(new MajorSimple(cellValue.trim()));
                                    pyfaZsBean.setMajorname(majorSimples.get(majorIndex).getId());
                                    break;
                                case 4:
                                    pyfaZsBean.setLimitYear(cellValue);
                                    break;
                                case 5:
                                    pyfaZsBean.setSyxw(cellValue);
                                    break;
                                case 6:
                                    pyfaZsBean.setMinCredit(cellValue);
                                    break;
                                case 7:
                                    pyfaZsBean.setMajorIntroduction(cellValue);
                                    break;
                                case 8:
                                    pyfaZsBean.setRequireContext(cellValue);
                                    break;
                                case 9:
                                    pyfaZsBean.setPlanTarget(cellValue);
                                    break;
                            }
                        }
                        break;
                        case NUMERIC:
                            switch (k) {
                                case 2:
                                    pyfaZsBean.setYear(cell.getNumericCellValue() + "");
                                    break;
                                case 4:
                                    pyfaZsBean.setLimitYear(cell.getNumericCellValue() + "");
                                    break;
                                case 6:
                                    pyfaZsBean.setMinCredit(cell.getNumericCellValue() + "");
                                    break;
                            }
                            break;
                    }
                }
            }

        } catch (Exception e) {
            e.printStackTrace();
        }
        return pyfaZsBean;
    }

    private static List<SemesterWeek> sheet2(HSSFWorkbook workbook) {
        List<SemesterWeek> semesterWeeks = new ArrayList<>();
        try {
            HSSFSheet sheet = workbook.getSheetAt(1);
            int physicalNumberOfRows = sheet.getPhysicalNumberOfRows();
            for (int j = 0; j < physicalNumberOfRows; j++) {
                if (j == 0) {
                    continue;//标题行
                }
                HSSFRow row = sheet.getRow(j);
                if (row == null) {
                    continue;//没数据
                }
                SemesterWeek semesterWeek = new SemesterWeek();
                int physicalNumberOfCells = row.getPhysicalNumberOfCells();
                for (int k = 0; k < physicalNumberOfCells; k++) {
                    HSSFCell cell = row.getCell(k);
                    switch (cell.getCellTypeEnum()) {
                        case STRING: {
                            String cellValue = cell.getStringCellValue();
                            if (cellValue == null) {
                                cellValue = "";
                            }
                            switch (k) {
                                case 0:
                                    semesterWeek.setSemester(cellValue);
                                    break;
                                case 1:
                                    semesterWeek.setWeek(cellValue);
                                    break;
                            }
                        }
                        break;
                        case NUMERIC:
                            switch (k) {
                                case 1:
                                    semesterWeek.setWeek(cell.getNumericCellValue()+"");
                                    break;
                            }
                            break;
                    }
                }
                semesterWeeks.add(semesterWeek);
            }

        } catch (Exception e) {
            e.printStackTrace();
        }
        return semesterWeeks;
    }

    private static List<MainCourse> sheet3(HSSFWorkbook workbook, List<CourseSimple> courseSimples) {
        List<MainCourse> mainCourses = new ArrayList<>();
        try {
            HSSFSheet sheet = workbook.getSheetAt(2);
            int physicalNumberOfRows = sheet.getPhysicalNumberOfRows();
            for (int j = 0; j < physicalNumberOfRows; j++) {
                if (j == 0) {
                    continue;//标题行
                }
                HSSFRow row = sheet.getRow(j);
                if (row == null) {
                    continue;//没数据
                }
                MainCourse mainCourse = new MainCourse();
                int physicalNumberOfCells = row.getPhysicalNumberOfCells();
                for (int k = 0; k < physicalNumberOfCells; k++) {
                    HSSFCell cell = row.getCell(k);
                    switch (cell.getCellTypeEnum()) {
                        case STRING: {
                            String cellValue = cell.getStringCellValue();
                            if (cellValue == null) {
                                cellValue = "";
                            }
                            switch (k) {
                                case 0:
                                    int courseIndex = courseSimples.indexOf(new CourseSimple(cellValue));
                                    mainCourse.setCourseName(courseSimples.get(courseIndex).getId());
                                    break;
                            }
                        }
                        break;
                    }
                }
                mainCourses.add(mainCourse);
            }

        } catch (Exception e) {
            e.printStackTrace();
        }
        return mainCourses;
    }

    private static List<TrainingRequire> sheet4(HSSFWorkbook workbook) {
        List<TrainingRequire> trainingRequires = new ArrayList<>();
        try {
            HSSFSheet sheet = workbook.getSheetAt(3);
            int physicalNumberOfRows = sheet.getPhysicalNumberOfRows();
            for (int j = 0; j < physicalNumberOfRows; j++) {
                if (j == 0) {
                    continue;//标题行
                }
                HSSFRow row = sheet.getRow(j);
                if (row == null) {
                    continue;//没数据
                }
                TrainingRequire trainingRequire = new TrainingRequire();
                int physicalNumberOfCells = row.getPhysicalNumberOfCells();
                for (int k = 0; k < physicalNumberOfCells; k++) {
                    HSSFCell cell = row.getCell(k);
                    switch (cell.getCellTypeEnum()) {
                        case STRING: {
                            String cellValue = cell.getStringCellValue();
                            if (cellValue == null) {
                                cellValue = "";
                            }
                            switch (k) {
                                case 0:
                                    trainingRequire.setRequire(cellValue);
                                    break;
                                case 1:
                                    trainingRequire.setCode(cellValue);
                                    break;
                            }
                        }
                        break;
                    }
                }
                trainingRequires.add(trainingRequire);
            }

        } catch (Exception e) {
            e.printStackTrace();
        }
        return trainingRequires;
    }

    private static List<CoursePlan> sheet5(HSSFWorkbook workbook, List<CourseSimple> courseSimples) {
        List<CoursePlan> coursePlans = new ArrayList<>();
        try {
            HSSFSheet sheet = workbook.getSheetAt(4);
            Integer widthLength = 0;
            int physicalNumberOfRows = sheet.getPhysicalNumberOfRows();
            for (int j = 0; j < physicalNumberOfRows; j++) {
                if (j == 0) {
                    widthLength = sheet.getRow(0).getPhysicalNumberOfCells();
                    continue;//标题行
                }
                HSSFRow row = sheet.getRow(j);
                if (row == null) {
                    continue;//没数据
                }
                CoursePlan coursePlan = new CoursePlan();
                /*int physicalNumberOfCells = row.getPhysicalNumberOfCells();*/
                for (int k = 0; k < widthLength; k++) {
                    HSSFCell cell = row.getCell(k);
                    if (cell==null) {
                        String cellValue = null;
                        switch (k) {
                            case 1:
                                coursePlan.setAllCredit(cellValue);
                                break;
                            case 2:
                                coursePlan.setLnxs(cellValue);
                                break;
                            case 3:
                                coursePlan.setSjxs(cellValue);
                                break;
                            case 4:
                                coursePlan.setXq1(cellValue);
                                break;
                            case 5:
                                coursePlan.setXq2(cellValue);
                                break;
                            case 6:
                                coursePlan.setXq3(cellValue);
                                break;
                            case 7:
                                coursePlan.setXq4(cellValue);
                                break;
                            case 8:
                                coursePlan.setXq5(cellValue);
                                break;
                            case 9:
                                coursePlan.setXq6(cellValue);
                                break;
                            case 10:
                                coursePlan.setXq7(cellValue);
                                break;
                            case 11:
                                coursePlan.setXq8(cellValue);
                                break;
                        }
                        continue;
                    }
                    switch (cell.getCellTypeEnum()) {
                        case STRING: {
                            String cellValue = cell.getStringCellValue();
                            switch (k) {
                                case 0:
                                    int courseIndex = courseSimples.indexOf(new CourseSimple(cellValue.trim()));
                                    coursePlan.setName(courseSimples.get(courseIndex).getId());
                                    break;
                                case 1:
                                    coursePlan.setAllCredit(cellValue);
                                    break;
                                case 2:
                                    coursePlan.setLnxs(cellValue);
                                    break;
                                case 3:
                                    coursePlan.setSjxs(cellValue);
                                    break;
                                case 4:
                                    coursePlan.setXq1(cellValue);
                                    break;
                                case 5:
                                    coursePlan.setXq2(cellValue);
                                    break;
                                case 6:
                                    coursePlan.setXq3(cellValue);
                                    break;
                                case 7:
                                    coursePlan.setXq4(cellValue);
                                    break;
                                case 8:
                                    coursePlan.setXq5(cellValue);
                                    break;
                                case 9:
                                    coursePlan.setXq6(cellValue);
                                    break;
                                case 10:
                                    coursePlan.setXq7(cellValue);
                                    break;
                                case 11:
                                    coursePlan.setXq8(cellValue);
                                    break;
                            }
                        }
                        break;
                        case NUMERIC: {
                            Double cellValueNumber = cell.getNumericCellValue();
                            String cellValue = null;
                            if (cellValueNumber != null) {
                                cellValue = cellValueNumber+"";
                            }
                            switch (k) {
                                case 1:
                                    coursePlan.setAllCredit(cellValue);
                                    break;
                                case 2:
                                    coursePlan.setLnxs(cellValue);
                                    break;
                                case 3:
                                    coursePlan.setSjxs(cellValue);
                                    break;
                                case 4:
                                    coursePlan.setXq1(cellValue);
                                    break;
                                case 5:
                                    coursePlan.setXq2(cellValue);
                                    break;
                                case 6:
                                    coursePlan.setXq3(cellValue);
                                    break;
                                case 7:
                                    coursePlan.setXq4(cellValue);
                                    break;
                                case 8:
                                    coursePlan.setXq5(cellValue);
                                    break;
                                case 9:
                                    coursePlan.setXq6(cellValue);
                                    break;
                                case 10:
                                    coursePlan.setXq7(cellValue);
                                    break;
                                case 11:
                                    coursePlan.setXq8(cellValue);
                                    break;
                            }
                        }
                        break;
                        case _NONE: {
                            String cellValue = null;
                            switch (k) {
                                case 1:
                                    coursePlan.setAllCredit(cellValue);
                                    break;
                                case 2:
                                    coursePlan.setLnxs(cellValue);
                                    break;
                                case 3:
                                    coursePlan.setSjxs(cellValue);
                                    break;
                                case 4:
                                    coursePlan.setXq1(cellValue);
                                    break;
                                case 5:
                                    coursePlan.setXq2(cellValue);
                                    break;
                                case 6:
                                    coursePlan.setXq3(cellValue);
                                    break;
                                case 7:
                                    coursePlan.setXq4(cellValue);
                                    break;
                                case 8:
                                    coursePlan.setXq5(cellValue);
                                    break;
                                case 9:
                                    coursePlan.setXq6(cellValue);
                                    break;
                                case 10:
                                    coursePlan.setXq7(cellValue);
                                    break;
                                case 11:
                                    coursePlan.setXq8(cellValue);
                                    break;
                            }

                        }
                        break;
                    }
                }
                coursePlans.add(coursePlan);
            }

        } catch (Exception e) {
            e.printStackTrace();
        }
        return coursePlans;
    }

    private static List<CourseStruction> sheet6(HSSFWorkbook workbook, List<TEntityCourseType> courseTypes) {
        List<CourseStruction> courseStructions = new ArrayList<>();
        try {
            HSSFSheet sheet = workbook.getSheetAt(5);
            Integer widthLength = 0;
            int physicalNumberOfRows = sheet.getPhysicalNumberOfRows();
            for (int j = 0; j < physicalNumberOfRows; j++) {
                if (j == 0) {
                    widthLength = sheet.getRow(0).getPhysicalNumberOfCells();
                    continue;//标题行
                }
                HSSFRow row = sheet.getRow(j);
                if (row == null) {
                    continue;//没数据
                }
                /*int physicalNumberOfCells = row.getPhysicalNumberOfCells();*/
                CourseStruction courseStruction = new CourseStruction();
                for (int k = 0; k < widthLength; k++) {
                    HSSFCell cell = row.getCell(k);
                    if (cell==null) {
                        String cellValue = null;
                        switch (k) {
                            case 1:
                                courseStruction.setAllCredit(cellValue);
                                break;
                            case 2:
                                courseStruction.setLnxs(cellValue);
                                break;
                            case 3:
                                courseStruction.setSjxs(cellValue);
                                break;
                            case 4:
                                courseStruction.setXqxf1(cellValue);
                                break;
                            case 5:
                                courseStruction.setXqxs1(cellValue);
                                break;
                            case 6:
                                courseStruction.setXqxf2(cellValue);
                                break;
                            case 7:
                                courseStruction.setXqxs2(cellValue);
                                break;
                            case 8:
                                courseStruction.setXqxf3(cellValue);
                                break;
                            case 9:
                                courseStruction.setXqxs3(cellValue);
                                break;
                            case 10:
                                courseStruction.setXqxf4(cellValue);
                                break;
                            case 11:
                                courseStruction.setXqxs4(cellValue);
                                break;
                            case 12:
                                courseStruction.setXqxf5(cellValue);
                                break;
                            case 13:
                                courseStruction.setXqxs5(cellValue);
                                break;
                            case 14:
                                courseStruction.setXqxf6(cellValue);
                                break;
                            case 15:
                                courseStruction.setXqxs6(cellValue);
                                break;
                            case 16:
                                courseStruction.setXqxf7(cellValue);
                                break;
                            case 17:
                                courseStruction.setXqxs7(cellValue);
                                break;
                            case 18:
                                courseStruction.setXqxf8(cellValue);
                                break;
                            case 19:
                                courseStruction.setXqxs8(cellValue);
                                break;
                        }
                        continue;
                    }
                    switch (cell.getCellTypeEnum()) {
                        case STRING: {
                            String cellValue = cell.getStringCellValue();
                            switch (k) {
                                case 0:
                                    int courseTypeIndex = courseTypes.indexOf(new TEntityCourseType(cellValue));
                                    courseStruction.setCourseType(courseTypes.get(courseTypeIndex).getId());
                                    break;
                                case 1:
                                    courseStruction.setAllCredit(cellValue);
                                    break;
                                case 2:
                                    courseStruction.setLnxs(cellValue);
                                    break;
                                case 3:
                                    courseStruction.setSjxs(cellValue);
                                    break;
                                case 4:
                                    courseStruction.setXqxf1(cellValue);
                                    break;
                                case 5:
                                    courseStruction.setXqxs1(cellValue);
                                    break;
                                case 6:
                                    courseStruction.setXqxf2(cellValue);
                                    break;
                                case 7:
                                    courseStruction.setXqxs2(cellValue);
                                    break;
                                case 8:
                                    courseStruction.setXqxf3(cellValue);
                                    break;
                                case 9:
                                    courseStruction.setXqxs3(cellValue);
                                    break;
                                case 10:
                                    courseStruction.setXqxf4(cellValue);
                                    break;
                                case 11:
                                    courseStruction.setXqxs4(cellValue);
                                    break;
                                case 12:
                                    courseStruction.setXqxf5(cellValue);
                                    break;
                                case 13:
                                    courseStruction.setXqxs5(cellValue);
                                    break;
                                case 14:
                                    courseStruction.setXqxf6(cellValue);
                                    break;
                                case 15:
                                    courseStruction.setXqxs6(cellValue);
                                    break;
                                case 16:
                                    courseStruction.setXqxf7(cellValue);
                                    break;
                                case 17:
                                    courseStruction.setXqxs7(cellValue);
                                    break;
                                case 18:
                                    courseStruction.setXqxf8(cellValue);
                                    break;
                                case 19:
                                    courseStruction.setXqxs8(cellValue);
                                    break;
                            }
                        }
                        break;
                        case NUMERIC:{
                            Double cellValueNumber = cell.getNumericCellValue();
                            String cellValue = null;
                            if (cellValueNumber != null) {
                                cellValue = cellValueNumber+"";
                            }
                            switch (k) {
                                case 1:
                                    courseStruction.setAllCredit(cellValue);
                                    break;
                                case 2:
                                    courseStruction.setLnxs(cellValue);
                                    break;
                                case 3:
                                    courseStruction.setSjxs(cellValue);
                                    break;
                                case 4:
                                    courseStruction.setXqxf1(cellValue);
                                    break;
                                case 5:
                                    courseStruction.setXqxs1(cellValue);
                                    break;
                                case 6:
                                    courseStruction.setXqxf2(cellValue);
                                    break;
                                case 7:
                                    courseStruction.setXqxs2(cellValue);
                                    break;
                                case 8:
                                    courseStruction.setXqxf3(cellValue);
                                    break;
                                case 9:
                                    courseStruction.setXqxs3(cellValue);
                                    break;
                                case 10:
                                    courseStruction.setXqxf4(cellValue);
                                    break;
                                case 11:
                                    courseStruction.setXqxs4(cellValue);
                                    break;
                                case 12:
                                    courseStruction.setXqxf5(cellValue);
                                    break;
                                case 13:
                                    courseStruction.setXqxs5(cellValue);
                                    break;
                                case 14:
                                    courseStruction.setXqxf6(cellValue);
                                    break;
                                case 15:
                                    courseStruction.setXqxs6(cellValue);
                                    break;
                                case 16:
                                    courseStruction.setXqxf7(cellValue);
                                    break;
                                case 17:
                                    courseStruction.setXqxs7(cellValue);
                                    break;
                                case 18:
                                    courseStruction.setXqxf8(cellValue);
                                    break;
                                case 19:
                                    courseStruction.setXqxs8(cellValue);
                                    break;
                            }
                        }
                        break;
                        case _NONE: {
                            String cellValue = null;
                            switch (k) {
                                case 1:
                                    courseStruction.setAllCredit(cellValue);
                                    break;
                                case 2:
                                    courseStruction.setLnxs(cellValue);
                                    break;
                                case 3:
                                    courseStruction.setSjxs(cellValue);
                                    break;
                                case 4:
                                    courseStruction.setXqxf1(cellValue);
                                    break;
                                case 5:
                                    courseStruction.setXqxs1(cellValue);
                                    break;
                                case 6:
                                    courseStruction.setXqxf2(cellValue);
                                    break;
                                case 7:
                                    courseStruction.setXqxs2(cellValue);
                                    break;
                                case 8:
                                    courseStruction.setXqxf3(cellValue);
                                    break;
                                case 9:
                                    courseStruction.setXqxs3(cellValue);
                                    break;
                                case 10:
                                    courseStruction.setXqxf4(cellValue);
                                    break;
                                case 11:
                                    courseStruction.setXqxs4(cellValue);
                                    break;
                                case 12:
                                    courseStruction.setXqxf5(cellValue);
                                    break;
                                case 13:
                                    courseStruction.setXqxs5(cellValue);
                                    break;
                                case 14:
                                    courseStruction.setXqxf6(cellValue);
                                    break;
                                case 15:
                                    courseStruction.setXqxs6(cellValue);
                                    break;
                                case 16:
                                    courseStruction.setXqxf7(cellValue);
                                    break;
                                case 17:
                                    courseStruction.setXqxs7(cellValue);
                                    break;
                                case 18:
                                    courseStruction.setXqxf8(cellValue);
                                    break;
                                case 19:
                                    courseStruction.setXqxs8(cellValue);
                                    break;
                            }
                        }
                        break;
                    }
                }
                courseStructions.add(courseStruction);
            }

        } catch (Exception e) {
            e.printStackTrace();
        }
        return courseStructions;
    }

    private static List<Gxjz> sheet7(HSSFWorkbook workbook, List<CourseSimple> courseSimples) {
        List<Gxjz> gxjzs = new ArrayList<>();
        try {
            HSSFSheet sheet = workbook.getSheetAt(6);
            int physicalNumberOfRows = sheet.getPhysicalNumberOfRows();
            Integer widthLength = 0;
            for (int j = 0; j < physicalNumberOfRows; j++) {
                if (j == 0) {
                    widthLength = sheet.getRow(0).getPhysicalNumberOfCells();
                    continue;//标题行
                }
                HSSFRow row = sheet.getRow(j);
                if (row == null) {
                    continue;//没数据
                }
                /*int physicalNumberOfCells = row.getPhysicalNumberOfCells();*/
                Gxjz gxjz = new Gxjz();
                Map<String,Boolean> map = new HashMap<>();
                String stringCellValue = row.getCell(0).getStringCellValue();
                int courseIndex = courseSimples.indexOf(new CourseSimple(stringCellValue));
                gxjz.setCourseName(courseSimples.get(courseIndex).getId());//TODO 有BUG
                for (int k = 1; k <= widthLength; k++) {
                    HSSFCell cell = row.getCell(k);
                    if (cell==null) {
                        map.put("r"+k,false);
                        continue;
                    }
                    String cellValue = cell.getStringCellValue();
                    if (cellValue.trim().equals("*")) {
                        map.put("r"+k,true);
                    } else {
                        map.put("r"+k,false);
                    }
                }
                gxjz.setCodeMap(map);
                gxjzs.add(gxjz);
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        return gxjzs;
    }
}
